package com.example.project.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.project.entity.IssueSimilarityCache;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * 需求相似度缓存Mapper接口
 */
@Mapper
public interface IssueSimilarityCacheMapper {

    /**
     * 根据源需求ID查找相似需求（用于IssueSimilarityService实现）
     */
    @Select("SELECT * FROM t_issue_similarity_cache WHERE source_issue_id = #{issueId} ORDER BY similarity_score DESC")
    List<IssueSimilarityCache> selectBySourceIssueId(@Param("issueId") Long issueId);

    /**
     * 根据源需求ID删除相似度记录
     */
    @Delete("DELETE FROM t_issue_similarity_cache WHERE source_issue_id = #{issueId}")
    int deleteBySourceIssueId(@Param("issueId") Long issueId);

    /**
     * 批量插入相似度记录
     */
    @Insert({
        "<script>",
        "INSERT INTO t_issue_similarity_cache (source_issue_id, target_issue_id, similarity_score, match_reasons, calculated_time) VALUES ",
        "<foreach collection='list' item='item' separator=','>",
        "(#{item.sourceIssueId}, #{item.targetIssueId}, #{item.similarityScore}, #{item.matchReasons}, NOW())",
        "</foreach>",
        "</script>"
    })
    int batchInsert(@Param("list") List<IssueSimilarityCache> caches);

    /**
     * 根据原始需求ID查找相似需求（用于其他功能）
     */
    @Select("SELECT * FROM t_issue_similarity_cache WHERE original_issue_id = #{originalIssueId} ORDER BY similarity_score DESC")
    List<IssueSimilarityCache> findByOriginalIssueId(@Param("originalIssueId") Integer originalIssueId);

    /**
     * 根据相似需求ID查找原始需求
     */
    @Select("SELECT * FROM t_issue_similarity_cache WHERE similar_issue_id = #{similarIssueId}")
    List<IssueSimilarityCache> findBySimilarIssueId(@Param("similarIssueId") Integer similarIssueId);

    /**
     * 查找两个需求之间的相似度记录
     */
    @Select("SELECT * FROM t_issue_similarity_cache WHERE original_issue_id = #{originalIssueId} AND similar_issue_id = #{similarIssueId}")
    IssueSimilarityCache findByIssuePair(@Param("originalIssueId") Integer originalIssueId, @Param("similarIssueId") Integer similarIssueId);

    /**
     * 查找高于指定相似度阈值的需求
     */
    @Select("SELECT * FROM t_issue_similarity_cache WHERE original_issue_id = #{originalIssueId} AND similarity_score >= #{threshold} ORDER BY similarity_score DESC")
    List<IssueSimilarityCache> findByOriginalIssueIdAndThreshold(@Param("originalIssueId") Integer originalIssueId, @Param("threshold") Double threshold);

    /**
     * 查找相似度最高的前N个需求
     */
    @Select("SELECT * FROM t_issue_similarity_cache WHERE original_issue_id = #{originalIssueId} ORDER BY similarity_score DESC LIMIT #{limit}")
    List<IssueSimilarityCache> findTopNSimilarIssues(@Param("originalIssueId") Integer originalIssueId, @Param("limit") Integer limit);

    /**
     * 根据项目ID查找相似需求
     */
    @Select("SELECT sc.* FROM t_issue_similarity_cache sc " +
            "JOIN t_issue i ON sc.similar_issue_id = i.id " +
            "WHERE sc.original_issue_id = #{originalIssueId} AND i.project_id = #{projectId} " +
            "ORDER BY sc.similarity_score DESC")
    List<IssueSimilarityCache> findByOriginalIssueIdAndProjectId(@Param("originalIssueId") Integer originalIssueId, @Param("projectId") Integer projectId);

    /**
     * 根据需求类别查找相似需求
     */
    @Select("SELECT sc.* FROM t_issue_similarity_cache sc " +
            "JOIN t_issue i ON sc.similar_issue_id = i.id " +
            "WHERE sc.original_issue_id = #{originalIssueId} AND i.tracker = #{tracker} " +
            "ORDER BY sc.similarity_score DESC")
    List<IssueSimilarityCache> findByOriginalIssueIdAndTracker(@Param("originalIssueId") Integer originalIssueId, @Param("tracker") String tracker);

    /**
     * 更新相似度评分
     */
    @Update("UPDATE t_issue_similarity_cache SET similarity_score = #{similarityScore} WHERE original_issue_id = #{originalIssueId} AND similar_issue_id = #{similarIssueId}")
    int updateSimilarityScore(@Param("originalIssueId") Integer originalIssueId, @Param("similarIssueId") Integer similarIssueId, @Param("similarityScore") Double similarityScore);

    /**
     * 更新关键词
     */
    @Update("UPDATE t_issue_similarity_cache SET keywords = #{keywords} WHERE original_issue_id = #{originalIssueId} AND similar_issue_id = #{similarIssueId}")
    int updateKeywords(@Param("originalIssueId") Integer originalIssueId, @Param("similarIssueId") Integer similarIssueId, @Param("keywords") String keywords);

    /**
     * 删除指定原始需求的所有相似度记录
     */
    @Update("DELETE FROM t_issue_similarity_cache WHERE original_issue_id = #{originalIssueId}")
    int deleteByOriginalIssueId(@Param("originalIssueId") Integer originalIssueId);

    /**
     * 删除指定需求的所有相似度记录
     */
    @Update("DELETE FROM t_issue_similarity_cache WHERE original_issue_id = #{issueId} OR similar_issue_id = #{issueId}")
    int deleteAllByIssueId(@Param("issueId") Integer issueId);

    /**
     * 查找超过指定天数的旧记录
     */
    @Select("SELECT * FROM t_issue_similarity_cache WHERE calculated_time < DATE_SUB(NOW(), INTERVAL #{days} DAY)")
    List<IssueSimilarityCache> findOldRecords(@Param("days") Integer days);

    /**
     * 根据多个条件查找相似需求
     */
    List<IssueSimilarityCache> findByConditions(Map<String, Object> conditions);

    /**
     * 获取指定项目的相似度统计
     */
    @Select("SELECT AVG(sc.similarity_score) as avg_score, COUNT(*) as count " +
            "FROM t_issue_similarity_cache sc " +
            "JOIN t_issue i ON sc.original_issue_id = i.id " +
            "WHERE i.project_id = #{projectId}")
    Map<String, Object> getSimilarityStatsByProjectId(@Param("projectId") Integer projectId);

    /**
     * 获取指定类别的相似度统计
     */
    @Select("SELECT AVG(sc.similarity_score) as avg_score, COUNT(*) as count " +
            "FROM t_issue_similarity_cache sc " +
            "JOIN t_issue i ON sc.original_issue_id = i.id " +
            "WHERE i.tracker = #{tracker}")
    Map<String, Object> getSimilarityStatsByTracker(@Param("tracker") String tracker);
}